package com.vmware.lm.test;
import java.sql.*;
//import java.util.Vector;

import java.util.*;

/**
 *<font face=arial size="2px"<b><p>
 * This is a lightweight JDBC class for MySQL. The goal of this class is to provide methods to
 * deal with dynamic SQL queries. Further it is an abstaction for database IO.
 * The goal was to hide the implementation details for JDBC connectivity
 * to provide a boilerplate means of handling SQL queries. The read mechanisms,
 * accept a select statement and return either an xml documnent or comma
 * delimited output. The write mechanisms process delete, insert, and update 
 * dml statements.</p>
 *<p></p><p>
 *Finally, the goal is to demonstrate how to create application specific
 *IO routines that implement this class. Reuse is what this language is all
 *about. Java is an awesome language for this. 
 * </p>
 * <p>
 * This class is not designed for production use. It intended use is for development. 
 * It can be used to provide a quick plugin to your application code for data access.
 * I have also used it to extract data, and to build xml documents.
 * </P>
 *<p><a href="http://www.sqlinfo.net">SQLInfo.net website</a></p>
 *<p><a href="http://www.dborchestra.com">dbOrchestra website</a></p>
 *</b></font>  
 *<h6>Syntax</h6>
 *<pre>&nbsp jdbc_mysql <i>tablename</i> = new jdbc_mysql();</pre> 
 *<h6>Example Usage</h6>
 *<pre>&nbsp jdbc_mysql question = new jdbc_mysql();</pre>  
 *
 *@author  Eric Matthews  (c)2002
 * 
**/
public class MySqlDataAccessHelper
{
  //class scoped variables          
    private Connection conn;
    
    //JDBC Drivers
    public static final String MYSQL_DRIVER = "com.mysql.jdbc.Driver"; 
    private String DefaultDriver = MYSQL_DRIVER;

    private String dbURL = "";
    //private String dbURL = "jdbc:mysql://127.0.0.1:3306/person";
    private String dbDatabase = ""; 
    private String user = "";
    private String pwd = "";
    
    private String tablename = "";
  
    private String PRIFLG = "F";

    private StringBuffer connString = new StringBuffer();
    //private String connString = "jdbc:mysql://127.0.0.1:3306/person?user=root&password=";
    private String jdbcDriver = MYSQL_DRIVER;

  public String setConnectionInfo(String url, String db, String usr, String pw, String jdbcDrv)
  { 
      StringBuffer returnMsg = new StringBuffer();
      if (jdbcDrv == "")
      {
         jdbcDriver =  DefaultDriver;
      } 
      else 
      {
         jdbcDriver =  jdbcDrv; 
      }
      returnMsg.append("using jdbcDriver: "); 
      returnMsg.append(jdbcDriver);       
      returnMsg.append(";"); 
      
      if (url == "")
      {
        returnMsg.append("URL parameter required, exiting"); 
        returnMsg.append(";");  
      } 
      else 
      { 
        connString.append(url);  
      }      

      if (db == "")
      {
         returnMsg.append("Database parameter required, exiting"); 
         returnMsg.append(";");
      } 
      else 
      {    
         connString.append(db);
      }       

      if (usr != "")
      {
        connString.append("?user=");     
        connString.append(usr);
      } 

      if (pw != "")
      {
        connString.append("&password=");
        connString.append(pw);  
      }       

   
      
      return returnMsg.toString();
  }



/**
 * 
 *<font face=arial size="2px"<b>
 *sqlRead(String qry) 
 *</b></font> 
 *<br>
 *<h6>Example Usage</h6>
 *<pre>sqlRead("Select * from Questions")</pre>
 *<pre>sqlRead("Select lastname, firstname from Person")</pre>
 *<p></p>
 *<p>
 *The method accepts a dml select statement and returns an XML
 *document.  This is a good example of encapsulation and keeping you 
 *from all of the evil details that one must deal with in order to
 *return an xml document for either all fields or a partial field list.
 *</p>  
 *<p></p>
 * 
*/
  public String sqlSelect(String qry)          
  {
  String preservedQry = qry;
  String xmlDoc = "";
  int cnt = 0;
  int ii = 0;

  int sz=2;
  int incr=2;
  Vector fldarr = new Vector(sz,incr);   
    
  String field = "";    
  qry = qry.trim();
  qry = qry.substring(6); //shag select
          
  char qryarr[] = qry.toCharArray();

/*
Please note, I think this would much better be done by using regular
expressions. Unfortunately at the time of this writing I was still using
1.2 and 1.3 so I would need to use an external 3rd party implementation
of regular expressions. I choose not to do so.

*/
//nasty, nasty stuff ...goal here is create a string array that contains each 
//field user specifies in their select statement. This would be two lines of code
//if we were using regular expressions.
   for (int i=0; i<qry.length(); i++)
   {    
    if (qryarr[i] != ' ')
    {
      if (qryarr[i] != ',')
      {     
        field += qryarr[i];
        cnt = 0;
      } 
    }
      
    if (qryarr[i] == ' '  || qryarr[i] == ',')
    {
      ++cnt;
        if (cnt > 1)
        {
          //do nothing  
        } 
        else if (cnt == 1)
        {
          field = field.toLowerCase();
          if (!(field.equals("from") )
              )
          //we got a field, add to array
          {             
            fldarr.addElement(field); 
            field = "";
          } 
          else 
          //we made it to the from and its time to quit loop     
          {
            break; 
          }
        }       

    }  //end if
              
  } //end for loop

  System.out.println(fldarr.size()); 

//determine which private method to call

      //they want all the fields
  if ((fldarr.elementAt(1).equals("*")))
  {
    xmlDoc = sqlReadAllColsReturnXML(preservedQry);     
  }
  else
  {
   //they only want some of the array, so send both the query and the
   //field array 
   xmlDoc = sqlReadSelectColsReturnXML(preservedQry,fldarr);         
   
  }
  return xmlDoc;      
 }


  private String sqlReadAllColsReturnXML(String qry)     
  {
    String xmlDoc = "";
    try
    {
    // Load driver
    Class.forName(jdbcDriver);

    // Create connection object
    conn = DriverManager.getConnection(connString.toString());
    Statement sql_stmt = conn.createStatement();

    // SQL dml statement
    ResultSet rs = 
      sql_stmt.executeQuery(qry);

    /*
       this object gives us access to important schema information from the
       resultset like the tablename and the columns names and the size of the
       resultset.
    */
    ResultSetMetaData rsmd = rs.getMetaData();
    int i = 1;

    // loop through recordset and print results
    int rownum = 1;
    
    if (PRIFLG == "F")
    {   
     tablename = rsmd.getTableName(i);
     PRIFLG = "T";
    } 
    
    
    //conditional exists because not all vendors support the rsmd.getTableName()
    //method. For example, it is not available in Access 97.
    if (tablename != "")
    {
      xmlDoc = ("<" + tablename + ">" + "\n"); //xml root node = the table name
    } else {
            xmlDoc = ("<" + "RootNode" + ">" + "\n");   
           }  
    while(rs.next()) //loop through rows in recordset
    {
     //we are creating an xml tag that represents row# in the recordset
     //  xmlDoc += (" <" + rownum + ">" + "\n");

    //# columns in recordset    
      for (i=1; i<=rsmd.getColumnCount(); i++) //loop through columns for each row
      {
                                //column name      
         xmlDoc += ("  <" + rsmd.getColumnLabel(i) + ">" + rs.getString(i) + "</" + rsmd.getColumnLabel(i) + ">" + "\n") ;
      } //end for loop
       //xmlDoc += (" </" + rownum++ + ">" + "\n");
    } //end while loop
    if (tablename != "")
    {
      xmlDoc += ("</" + tablename + ">" + "\n");
    } else {
      xmlDoc += ("</" + "RootNode" + ">" + "\n");   
           }      
    
    sql_stmt.close(); // clean up after ourself     
    } //end try block
    catch (Exception ex)
    {   
      xmlDoc += "  <SQLException>";
      xmlDoc += ex.toString(); // use expected xmlDoc to return error to caller
      xmlDoc += "  </SQLException>";
    }    
    return xmlDoc;
  } //end read method   


  private String sqlReadSelectColsReturnXML(String qry,Vector flds)          
  { 
    String xmlDoc = "";
    try
    {
    // Load driver
    Class.forName(jdbcDriver);

    // Create connection object
    conn = DriverManager.getConnection(connString.toString());
    Statement sql_stmt = conn.createStatement();

    // SQL dml statement
    ResultSet rs = 
      sql_stmt.executeQuery(qry);

    ResultSetMetaData rsmd = rs.getMetaData();
    int i = 1;

    // loop through recordset and print results
    int rownum = 1;


    if (PRIFLG == "F")
    {   
     tablename = rsmd.getTableName(i);
     PRIFLG = "T";
    } 
    if (tablename != "")
    {
      xmlDoc = ("<" + tablename + ">" + "\n"); //xml root node = the table name
    } else {
            xmlDoc = ("<" + "RootNode" + ">" + "\n");   
           }  
    while(rs.next()) //loop through rows in recordset
    {
     //we are creating an xml tag that represents row# in the recordset
     //  xmlDoc += (" <" + rownum + ">" + "\n");

    //# columns in recordset    
      for (i=1; i<flds.size(); i++) //loop through columns for each row
      {
                                //column name      
         xmlDoc += ("  <" + flds.elementAt(i) + ">" + rs.getString(i) + "</" + flds.elementAt(i) + ">" + "\n") ;
      } //end for loop
       //xmlDoc += (" </" + rownum++ + ">" + "\n");
    } //end while loop
    if (tablename != "")
    {
      xmlDoc += ("</" + tablename + ">" + "\n");
    } else {
      xmlDoc += ("</" + "RootNode" + ">" + "\n");   
           }      
    
    sql_stmt.close(); // clean up after ourself     
    } //end try block
    catch (Exception ex)
    {   
      xmlDoc += "  <SQLException>";
      xmlDoc += ex.toString(); // use expected xmlDoc to return error to caller
      xmlDoc += "  </SQLException>";
    }    
    return xmlDoc;
  } //end read method   


/**
 * 
 *<font face=arial size="2px"<b>
 *sqlRead(String qry) 
 *</b></font> 
 *<br>
 *<h6>Example Usage</h6>
 *<pre>sqlReadReturnCommaDelimited("Select * from Questions")</pre>
 *<p></p>
 *<p>
 *The method accepts a dml select statement and returns a comma delimited
 *string. At present I am only returning data. It could be enhanced to 
 *optionally return the first row with the column names. But hey, we do need
 *to leave some of the fun to others :-).
 *</p>  
 *<p></p>
 * 
*/
  public String sqlReadReturnCommaDelimited(String qry)          
  {
    String commadelimited = "";
    try
    {
    // Load driver
    Class.forName(jdbcDriver);

    // Create connection object
    conn = DriverManager.getConnection(connString.toString());
    Statement sql_stmt = conn.createStatement();

    // SQL dml statement
    ResultSet rs = 
      sql_stmt.executeQuery(qry);

    ResultSetMetaData rsmd = rs.getMetaData();
    int i = 1;

    // loop through recordset and print results
    int rownum = 1;

    while(rs.next()) //loop through rows in recordset
    {   
      for (i=1; i<=rsmd.getColumnCount(); i++) //loop through columns for each row
      {
        if (i != rsmd.getColumnCount())
        {  
         commadelimited += (rs.getString(i) + ",") ;
        }
        else
        { 
         commadelimited += (rs.getString(i)) ;  
        }   
      } //end for loop
      commadelimited +=  "\n";
    } //end while loop
    
                
    
    sql_stmt.close(); // clean up after ourself     
    } //end try block
    catch (Exception ex)
    {
      commadelimited += "  <SQLException>";             
      commadelimited += ex.toString(); // use expected xmlDoc to return error to caller
      commadelimited += "  </SQLException>";    
    }        
    return commadelimited;
  } //end read method   

    /**
    *<font face=arial size="2px"<b>
    * The method accepts a DML SELECT statement and returns column names and 
    * data in a Vector.  
    * The 1st element of the vector is '|' seperated column names in a string and rest
    * of the elements are '|" seperated data in String.
    *</b></font><br> 
    *
    * @param sqlQuery SQL SELECT statement.
    * @return column names and data in a vector.
    *<h6>Example Usage</h6>
    *<pre>sqlReadReturnVector("SELECT * FROM PERSON");</pre>  
	*<pre> Returns: 1st element of vector --> num|prim_xfaclt_num|last_nm|mid_nm|....</pre>
	*<pre>          2nd element of vector --> 1|0|Smith|S|.....</pre>
	*<pre>          3rd element of vector --> 2|0|Miller|T|.....</pre>
	*<pre>          .............
    */
    public Vector sqlReadReturnVector(String sqlQuery)
    {
        Vector readResult = new Vector();
        int colCount = 0;
        
        // convert to uppercase 
        sqlQuery = sqlQuery.toUpperCase();

        try
        {
            // Load driver
            Class.forName(jdbcDriver);
       
            // Create connection object
            conn = DriverManager.getConnection(connString.toString());
            
            // create the statement
            Statement stmt = conn.createStatement();
            
            ResultSet rs = stmt.executeQuery(sqlQuery);
          
            ResultSetMetaData rsmd = rs.getMetaData();
            StringBuffer colName = new StringBuffer("");
                    
            // total number of columns.
            colCount = rsmd.getColumnCount(); 
                       
            // create string of column names.
            for (int i=1; i <= colCount; i++)
            {
                colName.append(rsmd.getColumnName(i)).append("|");
            }
            
            // add column name string to vector.
            readResult.add(colName.toString());
            
            //read rows and add to the result vector
            while (rs.next())
            {
                StringBuffer rowData = new StringBuffer();
                for (int i=1; i <= colCount; i++)
                    rowData.append(rs.getString(i)).append("|");
                
                // add row to the result vector
                readResult.add(rowData.toString());
            } // end of while loop rs.next() = true            

            // close the statement
            stmt.close();
		} // end of try block
        catch (Exception exp)
        {
			// return error message in the exception.
			String str = "SQL Exception : " + exp.toString();
			readResult.add(str);
        }
        finally
        {        
            return readResult;
        }
    } // end of method sqlReadReturnVector()


/**
 * 
 *<font face=arial size="2px"<b>
 *sqlWrite(String qry) 
 *</b></font> 
 *<br>
 *<h6>Example Usage</h6>
 *<pre>sqlWrite("delete from Questions WHERE (Question_num = 16) ;")</pre>
 *<pre>sqlWrite("INSERT INTO Questions (Stem, Question_type) VALUES ('What color is a banana?', 'TF');")</pre>
 *<pre>sqlWrite("UPDATE" + " Questions SET Question_type = 'MC' WHERE Question_type = 'TF';")</pre> 
 *<p></p>
 *<p>
 *The method accepts a valid dml a dml update,insert,delete statement and
 *returns the status of the IO operation (example: number of rows updated, 
 *etc...) <i>Note: We have no need to get more granular (update, delete, insert) 
 *as each of these SQL IO operations uses the method executeUpdate().</i>
 *</p>  
 *<p></p>
 *<p>
 *If you desire to see the status of the method call...
 *</p>
 *result = <pre>sqlWrite("delete from Questions WHERE (Question_num = 16) ;")</pre>
 *<p>
 *In this context the status of the IO operation is returned to the caller. 0 means
 *the operation was not performed. A number >= 1 indicates the number of writes
 *performed. We also return the query string submitted, as well as any exceptions
 *that are thrown.
 *</p>
 * 
*/
  public String sqlWrite(String qry)          
  { 
    String result = "";
    try
    {
               //jdbc-odbc bridge
    // Load driver
    Class.forName(jdbcDriver);

    // Create connection object
    conn = DriverManager.getConnection(connString.toString());
    Statement sql_stmt = conn.createStatement();

    // SQL dml insert statement
    int a = sql_stmt.executeUpdate(qry);

    Integer wrap_a = new Integer(a);

    result = "<results>\n";
    result += "  <io_qty_performed>";
    result += wrap_a.toString(a);
    result += "    </io_qty_performed>\n";
    result += "  <query_submitted>";      
    result += qry;
    result += "  </query_submitted>\n";      
    result += "</results>";

    sql_stmt.close(); // clean up after ourself   
    } //end try block
    catch (SQLException ex)
    {
      result += "  <SQLException>"; 
      result += ex.toString();
      result += "  </SQLException>";          
    }    
    catch (Exception ex)
    {   
      result += "  <SQLException>"; 
      result += ex.toString();
      result += "  </SQLException>";    
    }
    closeConn();
    return result;  
  }

  private void closeConn()          
  { 
    try 
    {     
      conn.close(); 
      System.out.println("connection closed");  
    }
    catch (Exception e)
    {
      System.out.println("connection already closed");  
    }     
  } 

//--add schema services code
/**
 * 
 *<font face=arial size="2px"<b>
 *sqlSchema(String qry) 
 *</b></font> 
 *<br>
 *<h6>Example Usage</h6>
 *<pre>sqlSchema("Select * from Questions")</pre>
 *<p></p>
 *<p>
 *The method accepts a dml select statement and returns a field list. It
 *can also return other field related attributes like precision and data
 *type though this is not presently implemented
 *</p>  
 *<p></p>
 * 
*/
  public String sqlSchema(String qry)          
  {
  String preservedQry = qry;
  String strFlds = "";
  int cnt = 0;
  int ii = 0;

  int sz=2;
  int incr=2;
  Vector fldarr = new Vector(sz,incr);   
    
  String field = "";	
  qry = qry.trim();
  qry = qry.substring(6); //shag select
          
  char qryarr[] = qry.toCharArray();

   for (int i=0; i<qry.length(); i++)
   {	
    if (qryarr[i] != ' ')
    {
      if (qryarr[i] != ',')
      {    	
     	field += qryarr[i];
     	cnt = 0;
      }	
    }
      
    if (qryarr[i] == ' '  || qryarr[i] == ',')
    {
      ++cnt;
        if (cnt > 1)
        {
          //do nothing	
        } 
        else if (cnt == 1)
        {
          field = field.toLowerCase();
          if (!(field.equals("from") )
              )
          //we got a field, add to array
          {          	
            fldarr.addElement(field); 
            field = "";
          } 
          else 
          //we made it to the from and its time to quit loop     
          {
      	    break; 
          }
        } 		

    }  //end if
     	      
  } //end for loop

  System.out.println(fldarr.size()); 

//determine which private method to call

      //they want all the fields
  if ((fldarr.elementAt(1).equals("*")))
  {
    strFlds = schemaReadAllCols(preservedQry);  	
  }
  else
  {
   //they only want some of the array, so send both the query and the
   //field array 
   strFlds = schemaReadSelectCols(preservedQry,fldarr);		 
   
  }
  return strFlds;      
 }

  private String schemaReadSelectCols(String qry,Vector flds)          
  {	
    String strFlds = "";
    try
    {
    // Load driver
    Class.forName(jdbcDriver);

    // Create connection object
    conn = DriverManager.getConnection(connString.toString());
    Statement sql_stmt = conn.createStatement();

    // SQL dml statement
    ResultSet rs = 
      sql_stmt.executeQuery(qry);

    ResultSetMetaData rsmd = rs.getMetaData();
    int i = 1;

    // loop through recordset and print results
    int rownum = 1;
    tablename = rsmd.getTableName(i);

    if(rs.next()) //loop through rows in recordset
    {
    //# columns in recordset   	
      for (i=1; i<flds.size(); i++) //loop through columns for each row
      {
                                //column name      
        strFlds += (rsmd.getColumnLabel(i) + " " + "\n"   
                   //+ "~" +       //for future enhancements
                   //rsmd.getColumnTypeName(i) + "~" +
                   //rsmd.getPrecision(i) +  "\n"
                   ) ;   
      } //end if
    } //end while loop
     
    
    sql_stmt.close(); // clean up after ourself     
    } //end try block
    catch (Exception ex)
    {	
      strFlds += "  <SQLException>";
      strFlds += ex.toString(); // use expected strFlds to return error to caller
      strFlds += "  </SQLException>";
    }	 
    return strFlds;
  } //end read method	
  
  private String schemaReadAllCols(String qry)     
  {
    String strFlds = "";
    try
    {
    // Load driver
    Class.forName(jdbcDriver);

    // Create connection object
    System.out.println("dbJDBC" + connString + "user" + user);
    conn = DriverManager.getConnection(connString.toString());
    Statement sql_stmt = conn.createStatement();

    // SQL dml statement
    ResultSet rs = 
      sql_stmt.executeQuery(qry);

    /*
       this object gives us access to important schema information from the
       resultset like the tablename and the columns names and the size of the
       resultset.
    */
    ResultSetMetaData rsmd = rs.getMetaData();
    int i = 1;

    // loop through recordset and print results
    int rownum = 1;
    tablename = rsmd.getTableName(i);

    if(rs.next()) //loop through rows in recordset
    {

    //# columns in recordset   	
      for (i=1; i<=rsmd.getColumnCount(); i++) //loop through columns for each row
      {
                                //column name 
        strFlds += (rsmd.getColumnLabel(i) + " " + "\n" 
                   //+ "~" +        //for future enhancements
                   //rsmd.getColumnTypeName(i) + "~" +
                   //rsmd.getPrecision(i) +  "\n"
                   ) ;                             
      } //end if
    } //end while loop    
    
    sql_stmt.close(); // clean up after ourself     
    } //end try block
    catch (Exception ex)
    {	
      strFlds += "  <SQLException>";
      strFlds += ex.toString(); // use expected strFlds to return error to caller
      strFlds += "  </SQLException>";
    }	 
    return strFlds;
  } //end read method	  


//  public static void main(String args[])
//    {
//      // display row number of the vector. 1st -> column names, remaining data.
//      int i = 0; 
//
//      jdbc_mysql jdbc = new jdbc_mysql();
//      String ret = "";
//      ret = jdbc.setConnectionInfo("jdbc:mysql://127.0.0.1:3305/",
//                                   "person", 
//                                   "root", 
//                                   "homeboy1", 
//                                   MYSQL_DRIVER);
//       System.out.println(ret);
//       
//        String sqlStmt = "select * from person where num < 12";
// 
//        Vector v = jdbc.sqlReadReturnVector(sqlStmt);
//		System.out.println("\nSQL Statement: " + sqlStmt);
//	    for (Enumeration e = v.elements() ; e.hasMoreElements() ;)
//           	System.out.println(i++ + "--> " + e.nextElement());
//
//		// remove all elements from vector
//		v.clear();
//		i = 0;
// 
//        String str = jdbc.sqlSelect(sqlStmt);
//	System.out.println(str);
//
//        str = jdbc.sqlSchema(sqlStmt);
//	System.out.println(str);          
//                
//        sqlStmt = "select num, lastname, firstname from person where num > 0 and num < 4";
//      
//        str = jdbc.sqlReadReturnCommaDelimited(sqlStmt);
//	System.out.println(str);    
// 
//        str = jdbc.sqlSelect(sqlStmt);
//	System.out.println(str);
//
//        str = jdbc.sqlSchema(sqlStmt);
//	System.out.println(str);            
//        
//	System.out.println("\nThats all folks");
//        
//    } // end of method main()
//  
}  // end JDBC class
